package ass.dao.support;


import java.util.ArrayList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



import ass.beans.UserBean;
import ass.beans.UserFriendsBean;
import ass.common.DBConnectionFactory;
import ass.common.ServiceLocatorException;
import ass.dao.DataAccessException;
import ass.dao.UserDAO;

public class UserDAOImpl implements UserDAO {
	
	/**
	 * The service locator to retrieve database connections from
	 */
	
	private DBConnectionFactory services;
	
	/** Creates a new instance of UserDAOImpl */
	public UserDAOImpl() {
		try {
			services = new DBConnectionFactory();
		} catch (ServiceLocatorException e) {
			e.printStackTrace();
		}
	}

	public UserDAOImpl(DBConnectionFactory services) {
		this.services = services;
	}

	public void insert(UserBean bean) throws DataAccessException {
		Connection con = null;
		   try { 
		 con = services.createConnection();
	     PreparedStatement stmt = con.prepareStatement(
	       "INSERT INTO TBL_USERS (USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL, YEAR_OF_BIRTH, FAVOURITE_GENRE, FAVOURITE_ACTORS, IS_OWNER, ACTIVE, VERIFY_CODE) values (?, ?, ?, ?, ?, ?, ?, ?,?,?,?)");
	     stmt.setString(1, bean.getUsername());
	     stmt.setString(2, bean.getPassword());
	     stmt.setString(3, bean.getFirst_name());
	     stmt.setString(4, bean.getLast_name());
	     stmt.setString(5, bean.getEmail());
	     stmt.setString(6, bean.getYear_of_birth());
	     stmt.setString(7, bean.getFavourite_genre());
	     stmt.setString(8, bean.getFavourite_actors());
	     stmt.setString(9, bean.getIs_owner());
	     stmt.setString(10,bean.getActive());
	     stmt.setString(11,bean.getVerify_code());
	     
	     int n = stmt.executeUpdate();

	     stmt.close();
	     if (n != 1)
	       throw new DataAccessException("Did not insert one row into database");
	   } catch (ServiceLocatorException e) {
	       throw new DataAccessException("Unable to retrieve connection; " + e.getMessage(), e);
	   } catch (SQLException e) {
	       throw new DataAccessException("Unable to execute query; " + e.getMessage(), e);
	   } finally {
	      if (con != null) {
	         try {
	           con.close();
	         } catch (SQLException e1) {
	           e1.printStackTrace();
	         }
	      }
	   }
	}
	
	/**
	 * @todo Complete this method
	 * @see com.enterprise.dao.UserDAO#delete(int)
	 */
	public void update (String username, UserBean bean) throws DataAccessException {
		
		Connection con = null;
		   try {
			    con = services.createConnection();
				String sql = "UPDATE TBL_USERS "+
							 "SET "+
							"	FIRST_NAME = '"+bean.getFirst_name()+"', "+
							"	LAST_NAME = '"+bean.getLast_name()+"', "+
							"	EMAIL = '"+bean.getEmail()+"', "+
							"	YEAR_OF_BIRTH = '"+bean.getYear_of_birth()+"', "+
							"	FAVOURITE_GENRE = '"+bean.getFavourite_genre()+"', "+
							"	FAVOURITE_ACTORS = '"+bean.getFavourite_actors()+"', "+
							"	TICKET_TITLE = '"+bean.getTicket_title()+"', "+
							"	TICKET_CINEMA = '"+bean.getTicket_cinema()+"', "+
							"	TICKET_SHOW_TIME = '"+bean.getTicket_show_time()+"', "+
							"	TICKET_AMOUNT = "+bean.getTicket_amount()+", "+
							"	ACTIVE = '" +     bean.getActive() + "' "+
							" WHERE username = ?";
				System.out.println(sql);
				
	            PreparedStatement stmt = con.prepareStatement(sql.toString());
	            // run the SQL statement
	            stmt.setString(1, username);
	            int result = stmt.executeUpdate();
	            if (result == 0) {
	                System.out.println("Customer not updated.");
	            } else {
	                System.out.println("Customer updated.");
	            }
	            stmt.close();
		   } catch (ServiceLocatorException e) {
				throw new DataAccessException("Unable to retrieve connection; " + e.getMessage(), e);
		   } catch (SQLException e) {
				throw new DataAccessException("Unable to execute query; " + e.getMessage(), e);
		   } finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				}
			}
		
	}

	/**
	 * @todo Complete this method
	 * @see com.enterprise.dao.UserDAO#getUser(int)
	 */
	public UserBean getUser(String username) throws DataAccessException {

		   Connection con = null;
		   try {
		     con = services.createConnection();
		     PreparedStatement stmt = con.prepareStatement(
		       "select * from TBL_USERS where USERNAME = ?");
		     stmt.setString(1, username);
		     ResultSet rs = stmt.executeQuery();
				if (rs.next()) {
					System.out.println("getUSer");
					UserBean user = createUserBean(rs);
					return user;
				}
				stmt.close(); 
			} catch (ServiceLocatorException e) {
				throw new DataAccessException("Unable to retrieve connection; " + e.getMessage(), e);
			} catch (SQLException e) {
				throw new DataAccessException("Unable to execute query; " + e.getMessage(), e);
			} finally {
				if (con != null) {
					try {
						con.close();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				}
			}
			return null;
		}
	
	public UserBean createUserBean(ResultSet rs) throws SQLException {
		UserBean user = new UserBean();
		user.setUsername(rs.getString("username") );
		user.setPassword(rs.getString("password"));
	    user.setFirst_name(rs.getString("first_name") == null ? "": rs.getString("first_name"));
	    user.setLast_name(rs.getString("last_name") == null ? "": rs.getString("last_name"));
	    user.setEmail(rs.getString("email")== null ? "": rs.getString("email"));
	    user.setYear_of_birth(rs.getString("year_of_birth")== null ? "": rs.getString("year_of_birth"));
	    user.setFavourite_genre(rs.getString("favourite_genre")== null ? "": rs.getString("favourite_genre"));
	    user.setFavourite_actors(rs.getString("favourite_actors")== null ? "": rs.getString("favourite_actors"));
	    user.setIs_owner(rs.getString("is_owner")== null ? "": rs.getString("is_owner"));
	    user.setActive(rs.getString("active")== null ? "": rs.getString("active"));
		user.setVerify_code(rs.getString("verify_code")== null ? "": rs.getString("verify_code"));
//		TICKET_TITLE VARCHAR(100),
//		TICKET_CINEMA VARCHAR(100),
//		TICKET_SHOW_TIME VARCHAR(20),
//		TICKET_AMOUNT INTEGER,	
	    user.setTicket_title(rs.getString("TICKET_TITLE")== null ? "": rs.getString("TICKET_TITLE"));
	    user.setTicket_cinema(rs.getString("TICKET_CINEMA")== null ? "": rs.getString("TICKET_CINEMA"));
	    user.setTicket_show_time(rs.getString("TICKET_SHOW_TIME")== null ? "": rs.getString("TICKET_SHOW_TIME"));
		user.setTicket_amount((Integer) (rs.getInt("TICKET_AMOUNT")== 0 ? 0: rs.getInt("TICKET_AMOUNT")));
		
		return user;
	}

	public List<UserBean> getSearchedUser(String username, String firstName, String lastName, String email ) throws DataAccessException {
	List<UserBean> list = new ArrayList<UserBean>();
	Connection con = null;
	try {
		con = services.createConnection();
		// convert('2003-07-08',DATE)
		PreparedStatement stmt = con
				.prepareStatement("select * from TBL_USERS where (first_name like ? or last_name like ? or email like ?) and username !=  '"+username+"'");
		
		if (firstName.length() ==0){
			firstName = "#@!#";
		}else
			firstName = "%"+firstName+"%";
		if (lastName.length() ==0){
			lastName = "#@!#@!#";
		}else
			lastName = "%"+lastName+"%";
		if (email.length() ==0){
			email = "#@!#@!";
		}else
			email = "%"+email+"%";
		
		stmt.setString(1, firstName);
		stmt.setString(2, lastName);
		stmt.setString(3, email);
		System.out.println(stmt.toString());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			UserBean userBean = createUserBean(rs);
			list.add(userBean);
		}
		stmt.close();
	} catch (ServiceLocatorException e) {
		throw new DataAccessException("Unable to retrieve connection; "
				+ e.getMessage(), e);
	} catch (SQLException e) {
		throw new DataAccessException("Unable to execute query; "
				+ e.getMessage(), e);
	} finally {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}
	
	return list;
	}
	
}